package com.ruoyi.system.service.impl;

import java.sql.*;
import java.util.List;
import java.util.UUID;

import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.core.domain.entity.SysDept;
import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.system.domain.SysTable;
import com.ruoyi.system.mapper.SysTableMapper;
import com.ruoyi.system.service.ISysDeptService;
import com.ruoyi.system.service.ISysUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.ruoyi.system.mapper.SysFieldsMapper;
import com.ruoyi.system.domain.SysFields;
import com.ruoyi.system.service.ISysFieldsService;
import org.springframework.transaction.annotation.Transactional;

/**
 * 档案字段设置Service业务层处理
 * 
 * @author ruoyi——hsl
 * @date 2022-02-15
 */
@Service
public class SysFieldsServiceImpl implements ISysFieldsService 
{
    @Autowired
    private SysFieldsMapper sysFieldsMapper;

    @Autowired
    private ISysDeptService deptService;
    
    @Autowired
    private ISysUserService userService;

    @Autowired
    private SysTableMapper sysTableMapper;

    /**
     * 根据表编号获取档案字段设置
     * 
     * @param
     * @return 档案字段设置
     */
    @Override
    public List<SysFields> selectSysFieldsByTableId(SysFields sysFields)
    {
        return sysFieldsMapper.selectSysFieldsByTableIds(sysFields);
    }

    /**
     * 查询档案字段设置
     *
     * @param recNo 档案字段设置ID
     * @return 档案字段设置
     */
    @Override
    public SysFields selectSysFieldsById(String recNo) {
        return sysFieldsMapper.selectSysFieldsById(recNo);
    }

    /**
     * 查询档案字段设置列表
     * 
     * @param sysFields 档案字段设置
     * @return 档案字段设置
     */
    @Override
    public List<SysFields> selectSysFieldsList(SysFields sysFields)
    {
        return sysFieldsMapper.selectSysFieldsList(sysFields);
    }

    /**
     * 新增档案字段设置
     * 
     * @param sysFields 档案字段设置
     * @return 结果
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public AjaxResult insertSysFields(SysFields sysFields)
    {
        //设置UUID
        SysUser sysUser = userService.selectUserByUserName(SecurityUtils.getUsername());
        SysDept sysDept = deptService.selectDeptById(Long.parseLong(sysUser.getDeptId()));
        sysFields.setCreateBy(sysDept.getDeptName());
        sysFields.setCreateTime(DateUtils.getNowDate());
        sysFields.setDelFlag("0");
        //新增主键
        sysFields.setRecNo(UUid());
        sysFieldsMapper.insertSysFields(sysFields);
        //新增字段
        if (!insertFields(sysFields)){
            return AjaxResult.error("新增字段失败");
        }
        //正式表改动，临时表也要改动
        SysTable sysTable = sysTableMapper.selectSysTableById(sysFields.getTableNo());
        if ("1".equals(sysTable.getTableStatue())){
            SysTable sysTableTem = sysTableMapper.selectSysTableByName(sysTable.getTableName() + "_tem");
            sysFields.setRecNo(UUid());
            sysFields.setTableNo(sysTableTem.getRecNo());
            sysFieldsMapper.insertSysFields(sysFields);
            if (!insertFields(sysFields)){
                return AjaxResult.error("临时表新增字段失败");
            }
        }
        return AjaxResult.success();
    }

    /**
     * 修改档案字段设置
     * 
     * @param sysFields 档案字段设置
     * @return 结果
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public AjaxResult updateSysFields(SysFields sysFields)
    {
        SysUser sysUser = userService.selectUserByUserName(SecurityUtils.getUsername());
        SysDept sysDept = deptService.selectDeptById(Long.parseLong(sysUser.getDeptId()));
        sysFields.setUpdateBy(sysDept.getDeptName());
        sysFields.setUpdateTime(DateUtils.getNowDate());
        sysFieldsMapper.updateSysFields(sysFields);
        if (!updateFields(sysFields)){
            return AjaxResult.error("修改字段失败");
        }
        String tableStatue = sysTableMapper.selectSysTableById(sysFields.getTableNo()).getTableStatue();
        if ("1".equals(tableStatue)){
            SysFields sysFieldsByOne = sysFieldsMapper.selectSysFieldsByOne(sysFields);
            sysFieldsByOne.setUpdateBy(sysDept.getDeptName());
            sysFieldsByOne.setUpdateTime(DateUtils.getNowDate());

            sysFieldsMapper.updateSysFields(sysFieldsByOne);
            if (!updateFields_tem(sysFieldsByOne)){
                return AjaxResult.error("临时表修改字段失败");
            }
        }
        return AjaxResult.success();
    }

    /**
     * 批量删除档案字段设置
     * 
     * @param recNos 需要删除的档案字段设置ID
     * @return 结果
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public AjaxResult deleteSysFieldsByIds(String[] recNos)
    {
        for (String recNo : recNos) {
            SysFields sysFields = sysFieldsMapper.selectSysFieldsById(recNo);
            SysUser sysUser = userService.selectUserByUserName(SecurityUtils.getUsername());
            SysDept sysDept = deptService.selectDeptById(Long.parseLong(sysUser.getDeptId()));
            sysFields.setUpdateBy(sysDept.getDeptName());
            sysFields.setUpdateTime(DateUtils.getNowDate());
            sysFieldsMapper.deleteSysFieldsById(sysFields);
            if (!deleteFields(sysFields)){
                return AjaxResult.error("删除字段失败");
            }
            String tableStatue = sysTableMapper.selectSysTableById(sysFields.getTableNo()).getTableStatue();
            if ("1".equals(tableStatue)){
                SysFields sysFieldsByOne = sysFieldsMapper.selectSysFieldsByOne(sysFields);
                sysFieldsByOne.setUpdateBy(sysDept.getDeptName());
                sysFieldsByOne.setUpdateTime(DateUtils.getNowDate());

                sysFieldsMapper.deleteSysFieldsById(sysFieldsByOne);
                if (!deleteFields_tem(sysFieldsByOne)){
                    return AjaxResult.error("临时表删除字段失败");
                }
            }
        }
        return AjaxResult.success();
    }

    /**
     * 删除档案字段设置信息
     * 
     * @param sysFields 档案字段设置ID
     * @return 结果
     */
    @Override
    public int deleteSysFieldsById(SysFields sysFields)
    {
        return sysFieldsMapper.deleteSysFieldsById(sysFields);
    }

    /**
     * 新建表时建立默认字段
     * @param sysFields
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void insertSysFieldsByTable(SysFields sysFields) {
        //设置UUID
        SysUser sysUser = userService.selectUserByUserName(SecurityUtils.getUsername());
        SysDept sysDept = deptService.selectDeptById(Long.parseLong(sysUser.getDeptId()));
        sysFields.setCreateBy(sysDept.getDeptName());
        sysFields.setCreateTime(DateUtils.getNowDate());
        sysFields.setIsSort("0");
        sysFields.setDelFlag("0");
        //新增主键
        sysFields.setRecNo(UUid());
        sysFields.setFieldName("id");
        sysFields.setFieldAlias("主键");
        sysFields.setFieldType("int");
        sysFields.setFieldInputType("0");
        sysFields.setFieldLength("4");
        sysFields.setFieldDefaultValue("0");
        sysFields.setIsPk("0");
        sysFields.setIsNull("1");
        sysFields.setIsTitle("1");
        sysFields.setIsList("1");
        sysFields.setIsQuery("0");
        sysFields.setIsEdit("1");
        sysFields.setIsEncrypt("1");
        sysFields.setOrderNo(0);
        sysFieldsMapper.insertSysFields(sysFields);
        //新增名称
        sysFields.setRecNo(UUid());
        sysFields.setFieldName("ff");
        sysFields.setFieldAlias("名称");
        sysFields.setFieldType("varchar");
        sysFields.setFieldInputType("0");
        sysFields.setFieldLength("10");
        sysFields.setFieldDefaultValue("默认名称");
        sysFields.setIsPk("1");
        sysFields.setIsNull("1");
        sysFields.setIsTitle("1");
        sysFields.setIsList("0");
        sysFields.setIsQuery("0");
        sysFields.setIsEdit("0");
        sysFields.setIsEncrypt("1");
        sysFields.setOrderNo(1);
        sysFieldsMapper.insertSysFields(sysFields);
    }
    //封装新建字段
    @Transactional(rollbackFor = Exception.class)
    public Boolean insertFields(SysFields sysFields){
        try {
            SysTable sysTable = sysTableMapper.selectSysTableById(sysFields.getTableNo());
            Class.forName("com.mysql.jdbc.Driver");
            //创建连接
            String url = "jdbc:mysql://192.168.1.77:3306/archives?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true";
            Connection conn = DriverManager.getConnection(url, "root", "123456");
            Statement stat = conn.createStatement();
            //添加字段示例：alter table student add isDelete int(11) comment '11111111111';
            //字段名注释示例：comment  on  column  [表名].[字段名]   is  '注释内容';
            String creatsql = null;
            if (sysFields.getFieldDefaultValue().isEmpty()){//没有默认值
                creatsql = "alter table "+sysTable.getTableName()+" add "+sysFields.getFieldName()
                        +" "+sysFields.getFieldType()+"("+sysFields.getFieldLength()+") "
                        +" comment " +"'"+sysFields.getFieldAlias()+"';";
            }else {
                creatsql = "alter table " + sysTable.getTableName() + " add " + sysFields.getFieldName()
                        + " " + sysFields.getFieldType() + "(" + sysFields.getFieldLength() + ") "
                        + "default " + "'" + sysFields.getFieldDefaultValue() + "'"
                        + " comment " + "'" + sysFields.getFieldAlias() + "';";
            }
            //获取数据库表名
            ResultSet rs = conn.getMetaData().getTables(null, null, sysTable.getTableName(), null);
            // 判断表是否存在，如果存在则添加字段，否则失败
            if(rs.next()){
                stat.executeLargeUpdate(creatsql);
            }else {
                return false;
            }

            stat.close();
            conn.close();
            return true;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    //封装新建字段(正式表新增时，临时表一起新增)
    @Transactional(rollbackFor = Exception.class)
    public boolean insertFields_tem(SysFields sysFields) {
        try {
            SysTable sysTable = sysTableMapper.selectSysTableById(sysFields.getTableNo());
            Class.forName("com.mysql.jdbc.Driver");
            //创建连接
            String url = "jdbc:mysql://192.168.1.77:3306/archives?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true";
            Connection conn = DriverManager.getConnection(url, "root", "123456");
            Statement stat = conn.createStatement();
            //添加字段示例：alter table student add isDelete int(11) comment '11111111111';
            //字段名注释示例：comment  on  column  [表名].[字段名]   is  '注释内容';
            String creatsql = "alter table "+sysTable.getTableName()+"_tem add "+sysFields.getFieldName()
                    +" "+sysFields.getFieldType()+"("+sysFields.getFieldLength()+") "
                    +"default "+"'"+sysFields.getFieldDefaultValue()+"'"
                    +" comment " +"'"+sysFields.getFieldAlias()+"';";
            //获取数据库表名
            ResultSet rs = conn.getMetaData().getTables(null, null, sysTable.getTableName()+"_tem", null);
            // 判断表是否存在，如果存在则添加字段，否则失败
            if(rs.next()){
                stat.executeLargeUpdate(creatsql);
            }else {
                return false;
            }

            stat.close();
            conn.close();
            return true;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    //封装修改字段
    @Transactional(rollbackFor = Exception.class)
    public Boolean updateFields(SysFields sysFields){
        try {
            SysTable sysTable = sysTableMapper.selectSysTableById(sysFields.getTableNo());
            Class.forName("com.mysql.jdbc.Driver");
            //创建连接
            String url = "jdbc:mysql://192.168.1.77:3306/archives?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true";
            Connection conn = DriverManager.getConnection(url, "root", "123456");
            Statement stat = conn.createStatement();
            //备份表数据
            String creatsql = "create table "+sysTable.getTableName()+"_bak"+
                    " as select * from "+sysTable.getTableName()+";";
            //删除表数据
            String sql = "delete from "+sysTable.getTableName();
            SysFields sysFieldsOld = sysFieldsMapper.selectSysFieldsById(sysFields.getRecNo());
            //修改目标表结构示例：alter table MyTable change stuname sname varchar(10);
            //字段名注释示例：comment  on  column  [表名].[字段名]   is  '注释内容';
            String sql11 = "alter table "+sysTable.getTableName()
                    +" change "+sysFieldsOld.getFieldName()+" "+sysFields.getFieldName()+" "
                    +sysFields.getFieldType()+"("+sysFields.getFieldLength()+") "
                    +"default "+"'"+sysFields.getFieldDefaultValue()+"'"
                    +" comment "+"'"+sysFields.getFieldAlias()+"';";
            //将备份表数据重插回原表中
            String sql2 = "insert into "+sysTable.getTableName()+" select * from "+sysTable.getTableName()+"_bak;";
            //删掉备份表
            String sql3 = "drop table if exists "+sysTable.getTableName()+"_bak;";

            //获取数据库表名
            ResultSet rs = conn.getMetaData().getTables(null, null, sysTable.getTableName(), null);
            // 判断表是否存在，如果存在则修改
            if (rs.next()){
                stat.executeLargeUpdate(sql3);
                stat.executeLargeUpdate(creatsql);
                stat.executeLargeUpdate(sql);
                stat.executeLargeUpdate(sql11);
                stat.executeLargeUpdate(sql2);
                stat.executeLargeUpdate(sql3);
            }
            stat.close();
            conn.close();
            return true;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    //封装修改字段(正式表修改时，临时表一起修改)
    @Transactional(rollbackFor = Exception.class)
    public boolean updateFields_tem(SysFields sysFields) {
        try {
            SysTable sysTable = sysTableMapper.selectSysTableById(sysFields.getTableNo());
            Class.forName("com.mysql.jdbc.Driver");
            //创建连接
            String url = "jdbc:mysql://192.168.1.77:3306/archives?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true";
            Connection conn = DriverManager.getConnection(url, "root", "123456");
            Statement stat = conn.createStatement();
            //备份表数据
            String creatsql = "create table "+sysTable.getTableName()+"_tem_bak"+
                    " as select * from "+sysTable.getTableName()+"_tem;";
            //删除表数据
            String sql = "delete from "+sysTable.getTableName()+"_tem;";
            SysFields sysFieldsOld = sysFieldsMapper.selectSysFieldsById(sysFields.getRecNo());
            //修改目标表结构示例：alter table MyTable change stuname sname varchar(10);
            //字段名注释示例：comment  on  column  [表名].[字段名]   is  '注释内容';
            String sql11 = "alter table "+sysTable.getTableName()
                    +"_tem change "+sysFieldsOld.getFieldName()+" "+sysFields.getFieldName()+" "
                    +sysFields.getFieldType()+"("+sysFields.getFieldLength()+") "
                    +"default "+"'"+sysFields.getFieldDefaultValue()+"'"
                    +" comment "+"'"+sysFields.getFieldAlias()+"';";
            //将备份表数据重插回原表中
            String sql2 = "insert into "+sysTable.getTableName()+"_tem select * from "+sysTable.getTableName()+"_tem_bak;";
            //删掉备份表
            String sql3 = "drop table if exists "+sysTable.getTableName()+"_tem_bak;";

            //获取数据库表名
            ResultSet rs = conn.getMetaData().getTables(null, null, sysTable.getTableName()+"_tem", null);
            // 判断表是否存在，如果存在则修改
            if (rs.next()){
                stat.executeLargeUpdate(sql3);
                stat.executeLargeUpdate(creatsql);
                stat.executeLargeUpdate(sql);
                stat.executeLargeUpdate(sql11);
                stat.executeLargeUpdate(sql2);
                stat.executeLargeUpdate(sql3);
            }
            stat.close();
            conn.close();
            return true;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    //封装删除字段
    @Transactional(rollbackFor = Exception.class)
    public Boolean deleteFields(SysFields sysFields){
        try {
            SysTable sysTable = sysTableMapper.selectSysTableById(sysFields.getTableNo());
            Class.forName("com.mysql.jdbc.Driver");
            //创建连接
            String url = "jdbc:mysql://192.168.1.77:3306/archives?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true";
            Connection conn = DriverManager.getConnection(url, "root", "123456");
            Statement stat = conn.createStatement();
            //删除字段示例：alter table [表名] drop column [字段名]
            String creatsql = "alter table "+sysTable.getTableName()+" drop column "+sysFields.getFieldName()+";";
            //获取数据库表名
            ResultSet rs = conn.getMetaData().getTables(null, null, sysTable.getTableName(), null);
            // 判断表是否存在，如果存在则删除字段，否则失败
            if(rs.next()){
                stat.executeLargeUpdate(creatsql);
            }
            stat.close();
            conn.close();
            return true;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    //封装删除字段(正式表删除时，临时表一起删除)
    @Transactional(rollbackFor = Exception.class)
    public boolean deleteFields_tem(SysFields sysFields) {
        try {
            SysTable sysTable = sysTableMapper.selectSysTableById(sysFields.getTableNo());
            Class.forName("com.mysql.jdbc.Driver");
            //创建连接
            String url = "jdbc:mysql://192.168.1.77:3306/archives?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true";
            Connection conn = DriverManager.getConnection(url, "root", "123456");
            Statement stat = conn.createStatement();
            //删除字段示例：alter table [表名] drop column [字段名]
            String creatsql = "alter table "+sysTable.getTableName()+"_tem drop column "+sysFields.getFieldName()+";";
            //获取数据库表名
            ResultSet rs = conn.getMetaData().getTables(null, null, sysTable.getTableName()+"_tem", null);
            // 判断表是否存在，如果存在则删除字段，否则失败
            if(rs.next()){
                stat.executeLargeUpdate(creatsql);
            }
            stat.close();
            conn.close();
            return true;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    //去掉uuid的 ”-“
    public String UUid(){
        UUID uuid=UUID.randomUUID();
        String[] idd=uuid.toString().split("-");
        String id=idd[0]+idd[1]+idd[2]+idd[3]+ idd[4];
        return id;
    }
}
